
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/05/2011 10:29:21
-- Generated from EDMX file: C:\Users\Goran\Documents\Visual Studio 2010\Projects\Rail.Net\Rail.Net\Data\Model.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [RailNet];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Haltestellen]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Haltestellen];
GO
IF OBJECT_ID(N'[dbo].[TcvRelationen]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TcvRelationen];
GO
IF OBJECT_ID(N'[dbo].[Entfernungen]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Entfernungen];
GO
IF OBJECT_ID(N'[dbo].[TcvSerien]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TcvSerien];
GO
IF OBJECT_ID(N'[dbo].[Tcvps]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tcvps];
GO
IF OBJECT_ID(N'[dbo].[TcvRels]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TcvRels];
GO
IF OBJECT_ID(N'[dbo].[TcvDests]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TcvDests];
GO
IF OBJECT_ID(N'[dbo].[TcvVias]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TcvVias];
GO
IF OBJECT_ID(N'[dbo].[DomesticPrices]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DomesticPrices];
GO
IF OBJECT_ID(N'[dbo].[TrainSpecifics]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TrainSpecifics];
GO
IF OBJECT_ID(N'[dbo].[AgeLimits]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AgeLimits];
GO
IF OBJECT_ID(N'[dbo].[Tarifs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tarifs];
GO
IF OBJECT_ID(N'[dbo].[Borders]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Borders];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Haltestellen'
CREATE TABLE [dbo].[Haltestellen] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [Eva] nvarchar(7)  NOT NULL,
    [Epa] nvarchar(7)  NULL,
    [Tisp] nvarchar(4)  NULL,
    [Name] nvarchar(60)  NOT NULL,
    [ShortName] nvarchar(30)  NULL,
    [State] nvarchar(2)  NULL,
    [Border] bit  NULL,
    [Gtisp] nvarchar(4)  NULL,
    [Administration] nvarchar(4)  NULL,
    [Relation] int  NULL,
    [ShortName2] nvarchar(15)  NULL,
    [ForSale] bit  NULL,
    [X] nvarchar(15)  NULL,
    [Y] nvarchar(15)  NULL,
    [Eva0037] nvarchar(7)  NULL,
    [Tarif1] smallint  NULL,
    [Tarif2] smallint  NULL,
    [Tarif3] smallint  NULL
);
GO

-- Creating table 'TcvRelationen'
CREATE TABLE [dbo].[TcvRelationen] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [ValidFrom] datetime  NOT NULL,
    [ValidTo] datetime  NOT NULL,
    [RelationNumber] nvarchar(7)  NOT NULL,
    [BorderStationRu] nvarchar(4)  NOT NULL,
    [BorderStationNumber] nvarchar(6)  NOT NULL,
    [BorderStationName] nvarchar(30)  NOT NULL,
    [Ru1] nvarchar(6)  NOT NULL,
    [Ru2] nvarchar(6)  NOT NULL,
    [Ru3] nvarchar(6)  NOT NULL,
    [Distance1] int  NOT NULL,
    [Distance2] int  NOT NULL,
    [Distance3] int  NOT NULL,
    [Ru0] nvarchar(6)  NOT NULL,
    [Distance0] int  NOT NULL,
    [Route] nvarchar(150)  NOT NULL,
    [SerialPrice1] int  NOT NULL,
    [SerialPrice2] int  NOT NULL,
    [SerialPrice3] int  NOT NULL,
    [SerialPrice0] int  NOT NULL,
    [DestinationStation] nvarchar(12)  NOT NULL,
    [Border] bit  NOT NULL
);
GO

-- Creating table 'Entfernungen'
CREATE TABLE [dbo].[Entfernungen] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [TispTisp] nvarchar(8)  NOT NULL,
    [Oebb] int  NOT NULL,
    [RelationNumber] smallint  NOT NULL,
    [Db] int  NOT NULL,
    [Sbb] int  NOT NULL,
    [P10] int  NOT NULL,
    [P20] int  NOT NULL,
    [P30] int  NOT NULL,
    [P31] int  NOT NULL,
    [P40] int  NOT NULL,
    [P41] int  NOT NULL,
    [P42] int  NOT NULL,
    [P43] int  NOT NULL,
    [P50] int  NOT NULL,
    [P51] int  NOT NULL,
    [P52] int  NOT NULL,
    [P53] int  NOT NULL,
    [P60] int  NOT NULL,
    [P80] int  NOT NULL,
    [Mav] int  NOT NULL,
    [P81] int  NOT NULL
);
GO

-- Creating table 'TcvSerien'
CREATE TABLE [dbo].[TcvSerien] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [TarifNumber] int  NOT NULL,
    [Ru] nvarchar(6)  NOT NULL,
    [Serial] int  NOT NULL,
    [RoundTrip] nvarchar(1)  NOT NULL,
    [Class] nvarchar(1)  NOT NULL,
    [ValidFrom] datetime  NOT NULL,
    [ValidTo] datetime  NOT NULL,
    [Price] decimal(9,3)  NOT NULL
);
GO

-- Creating table 'Tcvps'
CREATE TABLE [dbo].[Tcvps] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [TarifNumber] int  NOT NULL,
    [Ru] nvarchar(6)  NOT NULL,
    [ZoneFrom] int  NOT NULL,
    [ZoneTo] int  NOT NULL,
    [RoundTrip] nvarchar(1)  NOT NULL,
    [Class] nvarchar(1)  NOT NULL,
    [ValidFrom] datetime  NOT NULL,
    [ValidTo] datetime  NOT NULL,
    [Price] decimal(9,3)  NOT NULL
);
GO

-- Creating table 'TcvRels'
CREATE TABLE [dbo].[TcvRels] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [RelationNumber] nvarchar(7)  NOT NULL,
    [BorderStation] nvarchar(9)  NOT NULL,
    [BorderStationAlternative] nvarchar(9)  NULL,
    [BorderDistanceKm] decimal(5,2)  NOT NULL,
    [ValidFrom] datetime  NOT NULL,
    [ValidTo] datetime  NOT NULL
);
GO

-- Creating table 'TcvDests'
CREATE TABLE [dbo].[TcvDests] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [RelationNumber] nvarchar(7)  NOT NULL,
    [DestinationStation] nvarchar(9)  NOT NULL,
    [Included] bit  NULL,
    [ValidFrom] datetime  NOT NULL,
    [ValidTo] datetime  NOT NULL
);
GO

-- Creating table 'TcvVias'
CREATE TABLE [dbo].[TcvVias] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [RelationNumber] nvarchar(7)  NOT NULL,
    [ViaStation] nvarchar(9)  NOT NULL,
    [Included] bit  NULL,
    [ValidFrom] datetime  NOT NULL,
    [ValidTo] datetime  NOT NULL
);
GO

-- Creating table 'DomesticPrices'
CREATE TABLE [dbo].[DomesticPrices] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [TarifNumber] int  NOT NULL,
    [Ru] nvarchar(2)  NOT NULL,
    [Class] nvarchar(1)  NOT NULL,
    [RoundTrip] nvarchar(1)  NOT NULL,
    [ZoneFrom] int  NOT NULL,
    [ZoneTo] int  NOT NULL,
    [Price] decimal(9,3)  NOT NULL,
    [Round] decimal(5,2)  NOT NULL,
    [ValidFrom] datetime  NOT NULL,
    [ValidTo] datetime  NOT NULL,
    [TrainType] smallint  NOT NULL,
    [Created] datetime  NOT NULL,
    [Input] nvarchar(20)  NOT NULL,
    [GroupId] nvarchar(10)  NOT NULL,
    [SellingSystem] smallint  NOT NULL
);
GO

-- Creating table 'TrainSpecifics'
CREATE TABLE [dbo].[TrainSpecifics] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [TrainLabel] nvarchar(10)  NOT NULL,
    [TrainNumber] nvarchar(10)  NOT NULL,
    [Ru] nvarchar(10)  NOT NULL
);
GO

-- Creating table 'AgeLimits'
CREATE TABLE [dbo].[AgeLimits] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [Ru] nvarchar(max)  NOT NULL,
    [Limit1] nvarchar(max)  NOT NULL,
    [Limit2] nvarchar(max)  NOT NULL,
    [Limit3] nvarchar(max)  NOT NULL,
    [Percent0] nvarchar(max)  NOT NULL,
    [Percent12] nvarchar(max)  NOT NULL,
    [Percent23] nvarchar(max)  NOT NULL,
    [Percent4] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Tarifs'
CREATE TABLE [dbo].[Tarifs] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Borders'
CREATE TABLE [dbo].[Borders] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [Eva] nvarchar(7)  NOT NULL,
    [Name] nvarchar(60)  NOT NULL,
    [EvaA] nvarchar(7)  NULL,
    [NameA] nvarchar(60)  NULL,
    [Correction] int  NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [Id] bigint IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [MiddleName] nvarchar(max)  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL,
    [Username] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [Birthday] nvarchar(max)  NOT NULL,
    [Gender] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Haltestellen'
ALTER TABLE [dbo].[Haltestellen]
ADD CONSTRAINT [PK_Haltestellen]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TcvRelationen'
ALTER TABLE [dbo].[TcvRelationen]
ADD CONSTRAINT [PK_TcvRelationen]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Entfernungen'
ALTER TABLE [dbo].[Entfernungen]
ADD CONSTRAINT [PK_Entfernungen]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TcvSerien'
ALTER TABLE [dbo].[TcvSerien]
ADD CONSTRAINT [PK_TcvSerien]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Tcvps'
ALTER TABLE [dbo].[Tcvps]
ADD CONSTRAINT [PK_Tcvps]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TcvRels'
ALTER TABLE [dbo].[TcvRels]
ADD CONSTRAINT [PK_TcvRels]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TcvDests'
ALTER TABLE [dbo].[TcvDests]
ADD CONSTRAINT [PK_TcvDests]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TcvVias'
ALTER TABLE [dbo].[TcvVias]
ADD CONSTRAINT [PK_TcvVias]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'DomesticPrices'
ALTER TABLE [dbo].[DomesticPrices]
ADD CONSTRAINT [PK_DomesticPrices]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TrainSpecifics'
ALTER TABLE [dbo].[TrainSpecifics]
ADD CONSTRAINT [PK_TrainSpecifics]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AgeLimits'
ALTER TABLE [dbo].[AgeLimits]
ADD CONSTRAINT [PK_AgeLimits]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Tarifs'
ALTER TABLE [dbo].[Tarifs]
ADD CONSTRAINT [PK_Tarifs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Borders'
ALTER TABLE [dbo].[Borders]
ADD CONSTRAINT [PK_Borders]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------